--------------------------------------------------------------------------------
-- @name: index key order
-- @author: dion cho
-- @note: test case for the impact of the index key order
--------------------------------------------------------------------------------

set pages 10000
set lines 200
set timing off
set trimspool on

@capture_on

-- multiple index key order for the equal predicate
drop table t1 purge;

create table t1(c1, c2)
as
select decode(mod(level,2),0,'A','B'), mod(level, 1000)
from dual
connect by level <= 100000;

create index t1_n1 on t1(c1, c2);

create index t1_n2 on t1(c2, c1);

exec dbms_stats.gather_table_stats(user, 't1');

-- case 1: equal predicates
select /*+ gather_plan_statistics index(t1 t1(c1, c2)) */
	count(*)
from t1
where c1 = 'A' and c2 = 10;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


select /*+ gather_plan_statistics index(t1 t1(c2, c1)) */
	count(*)
from t1
where c1 = 'A' and c2 = 10;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- case 2: skip scan
select /*+ gather_plan_statistics full(t1) */
	count(*)
from t1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


select /*+ gather_plan_statistics index_ss(t1 t1(c1, c2)) */
	count(*)
from t1
where c2 = 10;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index_ss(t1 t1(c2, c1)) */
	count(*)
from t1
where c1 = 'A';

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));



-- case2: size 
truncate table t1;

insert into t1
select decode(mod(level,2),0,'A','B'), mod(level, 1000)
from dual
connect by level <= 500000
order by 1, 2;

exec dbms_stats.gather_table_stats(user, 't1');

select index_name, leaf_blocks from user_indexes where table_name = 'T1' order by 1;


truncate table t1;

insert into t1
select decode(mod(level,2),0,'A','B'), mod(level, 1000)
from dual
connect by level <= 500000
order by 2, 1;

exec dbms_stats.gather_table_stats(user, 't1');

select index_name, leaf_blocks from user_indexes where table_name = 'T1' order by 1;








@capture_off
